R Notebook

#install.packages("knitr")
#install.packages("grid")
#install.packages("MLRMPA")
#install.packages("dprep")
#install.packages("normalr")
#install.packages("ggcorrplot")
#install.packages("RColorBrewer")
#install.packages("rgdal")
#install.packages("jsonlite")
#install.packages("RColorBrewer")
#install.packages("readr")
Sys.setlocale("LC_ALL", "English")
Sys.setenv("LANGUAGE"="En")

library(gridExtra)
library(dplyr)
library(lubridate)
library(magrittr)
library(ggplot2)
library(tidyr)
library(knitr)
library(normalr)
library(ggcorrplot)
library(leaflet)
library(plotly)
library(RColorBrewer)
library(readr)

#??src_mysql
my_db <- src_mysql(
  dbname = "covid",
  host = "localhost",
  user = "root",
  password = "1234"
)
my_db

##import data
df_conf <- tbl(my_db, sql("select * from covid19_confirmed"))
df_conf <- as.data.frame(df_conf)
df_conf
df_deaths <- tbl(my_db, sql("select * from covid19_deaths"))
df_deaths <- as.data.frame(df_deaths)
df_deaths
df_recover <- tbl(my_db, sql("select * from covid19_recovered"))
df_recover <- as.data.frame(df_recover)
df_recover

##check the time frame of the data
n.col <- ncol(df_conf)
dates <- names(df_conf)[5:n.col]%>% mdy()
range(dates)
min.date <- min(dates)
max.date <- max(dates)
min.date.txt <- min.date %>% format('%d %b %Y')
max.date.txt <- max.date %>% format('%d %b %Y')

#clean data
cleanData <- function(data) {
  ## remove some columns
  data %<>% select(-c(Province.State, Lat, Long)) %>% rename(country=Country.Region)
  ## convert from wide to long format
  data %<>% gather(key=date, value=count, -country)
  ## convert from character to date
  data %<>% mutate(date = date %>% mdy())
  ## aggregate by country
  data %<>% group_by(country, date) %>% summarise(count=sum(count, na.rm=T)) %>% as.data.frame()
  return(data)
}
## clean the three data sets
data.confirmed <- df_conf %>% cleanData() %>% rename(confirmed=count)
data.deaths <- df_deaths %>% cleanData() %>% rename(deaths=count)
data.recovered <- df_recover %>% cleanData() %>% rename(recovered=count)
data <- data.confirmed %>% merge(data.deaths, all=T) %>% merge(data.recovered, all=T)
data
## countries/regions with confirmed cases, excl. cruise ships
countries <- data %>% pull(country) %>% setdiff('Cruise Ship')
data
data.world <- data %>% group_by(date) %>%
  summarise(country='World',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T),
            recovered = sum(recovered, na.rm=T))
data %<>% rbind(data.world)
data
data %<>% mutate(current.confirmed = confirmed - deaths - recovered)
View(data)

x <- raw.confirmed
x$confirmed <- x[, ncol(x)]
x %<>% select(c(Country.Region, Province.State, Lat, Long, confirmed)) %>%
mutate(txt=paste0(Country.Region, ' - ', Province.State, ': ', confirmed))
m <- leaflet(width=1200, height=800) %>% addTiles()
# circle marker (units in pixels)
m %<>% addCircleMarkers(x$Long, x$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=0.03*sqrt(x$confirmed),
                        stroke=F,
                        color='red', fillOpacity=0.3,
                        label=x$txt)
# world
m

## China

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 
6: Unknown or uninitialised column: `Country`. 
7: Unknown or uninitialised column: `Country`. 
8: Unknown or uninitialised column: `Country`. 
9: Unknown or uninitialised column: `Country`. 

m %>% setView(95, 35, zoom=4)

## Australia and New Zealand
m %>% setView(135, -27, zoom=4)

## US and Canada
m %>% setView(-105, 40, zoom=4)

## Europe
m %>% setView(10, 50, zoom=4)

NA

# new.confirmed new.deaths new.recovered
data %<>% arrange(country, date)
n <- nrow(data)
day1 <- min(data$date)
data %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1, NA, recovered - lag(recovered, n=1)))
data %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

View(data)

## convert from wide to long format

Warning message:
Unknown or uninitialised column: `Country`. 

data.long <- data %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
View(data.long)

df <- data.long %>% filter(country %in% top.countries) %<>%

There were 14 warnings (use warnings() to see them)

  mutate(country=country %>% factor(levels=c(top.countries)))
df %>% filter(country != 'World' & type != 'Total Confirmed') %>%
  ggplot(aes(x=date, y=count, color=type)) +
  geom_line()+
  #geom_area(alpha=0.5) +
# xlab('') + ylab('') +
  labs(title=paste0('Numbers of COVID-19 Cases in Top 20 Countries - ',
                    max.date.txt)) +
  scale_color_manual(values=c('red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=12),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.4, 'cm'),
        legend.text=element_text(size=12),
        strip.text.x=element_text(size=12),
        axis.text=element_text(size=12),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~country, ncol=4, scales='free_y') +
  scale_y_continuous(trans='log10')

##Number of case World

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 
6: Unknown or uninitialised column: `Country`. 
7: Unknown or uninitialised column: `Country`. 
8: Unknown or uninitialised column: `Country`. 

world <- filter(data.long,country == 'World')
world

plot1 <- world %>% filter(type != 'Total Confirmed') %>%
  ggplot(aes(x=date, y=count)) +
  geom_area(aes(fill=type), alpha=0.5) +
  labs(title=paste0('Numbers of Cases Worldwide - ', max.date.txt)) +
  scale_fill_manual(values=c('red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1))
plot2 <- world %>%
  ggplot(aes(x=date, y=count)) +
  geom_line(aes(color=type)) +
  labs(title=paste0('Numbers of Cases Worldwide (log scale) - ', max.date.txt)) +
  scale_color_manual(values=c('purple', 'red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1)) +
  scale_y_continuous(trans='log10')
## show two plots side by side
grid.arrange(plot1, plot2, ncol=2)

gly.plot1 <- ggplotly(plot1)
gly.plot1

gly.plot2 <- ggplotly(plot2)
gly.plot2

## Current Confirmed Cases

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

data.world <- data %>% filter(country=='World')
n <- nrow(data.world)
plot1 <- ggplot(data.world, aes(x=date, y=current.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Current Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.world, aes(x=date, y=new.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Daily New Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show two plots side by side
grid.arrange(plot1, plot2, ncol=2)

View(data.world)

## a scatter plot with a smoothed line and vertical x-axis labels

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

plot1 <- ggplot(data.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot3 <- ggplot(data.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot4 <- ggplot(data.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot1, plot2, plot3, plot4, nrow=2)

## convert from wide to long format, for drawing area plots

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

rates.long <- data %>%
  select(c(country, date, rate.upper, rate.lower, rate.daily)) %>%
  gather(key=type, value=count, -c(country, date))
# set factor levels to show them in a desirable order
rates.long %<>% mutate(type=recode_factor(type, rate.daily='Daily',
                             rate.upper='Upper bound',
                             rate.lower = 'Lower bound')) 
#View(rates.long) 

g <- rates.long %>% filter(country == "World") %>% 
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() +
  labs(title = "World Death Rate (%)") +
  xlab("") + ylab("Death Rate (%)")

g

gly.death <- ggplotly(g)
gly.death

## ranking by confirmed cases

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

data.latest.all <- data %>% filter(date == max(date)) %>%
  select(country, date,confirmed, new.confirmed, current.confirmed,
         recovered, deaths, new.deaths, death.rate=rate.lower) %>%
  mutate(ranking = dense_rank(desc(confirmed))) %>%
  arrange(ranking)
View(data.latest.all)

k <- 20
## top 20 countries: 21 incl. 'World'
top.countries <- data.latest.all %>% filter(ranking <= k + 1) %>%
  arrange(ranking) %>% pull(country) %>% as.character()
top.countries %>% setdiff('World') %>% print()

 [1] "US"             "India"          "Brazil"         "Russia"         "United Kingdom" "France"         "Spain"          "Italy"         
 [9] "Turkey"         "Germany"        "Colombia"       "Argentina"      "Mexico"         "Poland"         "South Africa"   "Iran"          
[17] "Ukraine"        "Peru"           "Indonesia"      "Netherlands"   

data.latest <- data.latest.all %>% filter(!is.na(country)) %>%

There were 11 warnings (use warnings() to see them)

  mutate(country=ifelse(ranking <= k + 1, as.character(country), 'Others')) %>%
  mutate(country=country %>% factor(levels=c(top.countries, 'Others')))
data.latest %<>% group_by(country) %>%
  summarise(confirmed=sum(confirmed), new.confirmed=sum(new.confirmed),
            current.confirmed=sum(current.confirmed),
            recovered=sum(recovered), deaths=sum(deaths), new.deaths=sum(new.deaths)) %>%
  mutate(death.rate=(100 * deaths/confirmed) %>% round(1)) 

`summarise()` ungrouping output (override with `.groups` argument)

data.latest

data.latest %<>% select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths, current.confirmed,recovered)) %>%
  mutate(recover.rate=(100 * recovered/confirmed) %>% round(1))
data.latest

df_pop <- tbl(my_db, sql("select * from population "))
df_pop <- as.data.frame(df_pop)
df_pop <- rename(df_pop,"country"="Country")

# Add World Population
world_pop <- sum(df_pop$`Population (2020)`)
df_pop[nrow(df_pop) + 1,] = c("World", world_pop)

# Add Other Countries Population
top_pop <- filter(df_pop, df_pop$country %in% top.countries & df_pop$country != "World")
top_pop <- sum(top_pop$`Population (2020)` %>% as.numeric())
others_pop <- (world_pop - top_pop) 
df_pop[nrow(df_pop) + 1,] = c("Others", others_pop)
View(df_pop)

data.latest <- merge(x = data.latest, y = df_pop, by = "country", all.x = TRUE) 
data.latest

data.latest <- rename(data.latest,"population" = "Population (2020)")
data.latest$population <- data.latest$population %>% as.numeric()
data.latest  <- data.latest %>%
  select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths,
                          current.confirmed, recovered, recover.rate, population)) %>%
  mutate(confirm.rate = (100 * confirmed / population) %>% round(1))
data.latest

data.latest %>% mutate(death.rate=death.rate %>% format(nsmall=1) %>% paste0('%'))

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

## convert from wide to long format, for drawing area plots

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

data.latest.long <- data.latest %>% filter(country!='World') %>%
  gather(key=type, value=count, -country)
## set factor levels to show them with proper text and in a desirable order
data.latest.long %<>% mutate(type=recode_factor(type,
                                                confirmed='Total Confirmed',
                                                deaths='Total Deaths',
                                                death.rate='Death Rate (%)',
                                                new.confirmed='New Confirmed (compared with one day before)',
                                                new.deaths='New Deaths (compared with one day before)',
                                                current.confirmed='Current Confirmed',
                                                recover.rate = 'Recovered Rate(%)',
                                                confirm.rate = 'Confirmed Rate(%)'))
#View(data.latest.long)
data.one.dem <- filter(data.latest.long,type=='Total Confirmed'
                       | type=='Total Deaths'
                       | type=='Current Confirmed')
data.two.dem <- filter(data.latest.long,type=='Death Rate (%)'
                     #  | type=='New Confirmed (compared with one day before)'
                    #   | type=='New Deaths (compared with one day before)'
                       | type=='Recovered Rate(%)'
                       | type=='Confirmed Rate(%)')
data.two.dem

## bar chart

Warning message:
Unknown or uninitialised column: `Country`. 

data.one.dem %>% ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~type, ncol=1, scales='free_y')

data.two.dem$facet <- factor(data.two.dem$type, levels = c('Confirmed Rate(%)', 'Recovered Rate(%)','Death Rate (%)'))

Warning message:
Unknown or uninitialised column: `Country`. 

data.two.dem %>% 
  ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=4, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=10),
        axis.text.x=element_text(size=10,angle=45, hjust=1)) +
  facet_wrap(~facet, ncol=1, scales='free_y')

##GDP

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

df_gdp <- tbl(my_db, sql("select * from gdp"))
df_gdp <- as.data.frame(df_gdp)
df_gdp <- rename(df_gdp,"country"="Real GDP growth (Annual percent change)")
df_gdp <- select(df_gdp,c("country","2012","2013","2014","2015","2016","2017","2018","2019","2020","2021"))
df_gdp

df_gdp2019 <- tbl(my_db, sql("select * from gdp19"))
df_gdp2019 <- as.data.frame(df_gdp2019)
df_gdp2019

NA

#healthranking

Warning message:
Unknown or uninitialised column: `Country`. 

df_healt <- tbl(my_db, sql("select * from healthranking"))
df_healt <- as.data.frame(df_healt)
df_healt <- select(df_healt,c("country","healthCareIndex"))
View(df_healt)

#Top20Pornhub

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

df_pornhub <- tbl(my_db, sql("select * from Pornhub"))
df_pornhub <- as.data.frame(df_pornhub)
df_pornhub

#temp
df_temp <- tbl(my_db, sql("select * from world_temp"))
df_temp <- as.data.frame(df_temp) 
df_temp$Country[df_temp$Country == "United States"] <- "US"

df_city <- select(df_temp,c("Country","City")) %>%
  rename(country=Country) %>% 
  rename(city=City)

numofcity <- aggregate(city ~ country, data = df_city, length)

df_temp <- select(df_temp,c("Country","Avg_Year")) %>%
  rename(country=Country)
View(df_temp)

#df_temp <- data.frame(country=df_temp[,1],avg=rowMeans(df_temp[,-1]))
df_temp <- df_temp %<>% group_by(country) %>% summarise(avg_temp = mean(Avg_Year,na.rm = TRUE)%>% round(1))

`summarise()` ungrouping output (override with `.groups` argument)

df_temp

#display.brewer.all()

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

df_temp.all <- df_temp %>% merge(data.latest.all)
View(df_temp.all)
df_temp_top.all <- df_temp.all %>% filter(country %in% top.countries) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
View(df_temp_top)
g_temp_top <- df_temp_top %>%
  ggplot(aes(x = reorder(country, ranking), y = avg_temp, fill = avg_temp)) +
  labs(title=paste0("Temperature in Top  20 countries"), subtitle = "Average Temperature in Top 20 countries with most confirmed cases (°C) (2020)") +
  scale_color_gradient(low = "#93DBFF", high = "#FF7771") +
  geom_text(aes(label=avg_temp, y=avg_temp), size=4, vjust=-0.5) +
  geom_bar(stat = "identity", position = "dodge") +
  theme(
        legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size = 15, hjust = 0.5),
        plot.subtitle = element_text(size = 12, hjust = 0.5),
        axis.text=element_text(size=8),
        axis.text.x=element_text(size = 9, angle=45, hjust=1)) +
  scale_x_discrete(name = "Country") +
  scale_y_discrete(name = "Average Temperature")

           #labs(title = "Temperature in Top  20 countries", subtitle = "Temperature in Top 20 countries with most confirmed cases (°C)")
         
#g_temp_top         
g_temp_top 

#df_conf

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 
6: Unknown or uninitialised column: `Country`. 
7: Unknown or uninitialised column: `Country`. 
8: Unknown or uninitialised column: `Country`. 
9: Unknown or uninitialised column: `Country`. 

#data.latest.all
lat.long <- rename(df_conf, "country" = "Country.Region", "city" = "Province.State") %>% 
  select("country", "Lat", "Long") %>% 
  merge(df_temp.all[c("country","confirmed", "recovered", "deaths", "avg_temp", "ranking")], by = "country") %>%
  distinct(country, .keep_all = TRUE) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
View(lat.long)

label_world <- lat.long 

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

label_world$avg_temp <- as.numeric(label_world[, names(label_world) %in% c("avg_temp")])
label_world <- label_world %>%  
  mutate(txt=paste0('<b>',ranking, '</b>',
                    '<br/>','<b>',country, '</b>',
                    '<br/>', "Temperature:  ",avg_temp, ' °C',
                    '<br/>', "Confirmed:  ", confirmed, 
                    '<br/>', "Deaths: ", deaths,
                    '<br/>', "Recovered: ", recovered
                    )) 

label_world$txt <- label_world$txt %>% lapply(htmltools::HTML)
label_world 

label_top <- label_world %>% filter(ranking < 21)
label_top

wpal <- colorNumeric("YlOrRd", label_world$avg_temp, n = 4)

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

topIcon <- makeIcon("star.png",
  #iconUrl = "https://static.vecteezy.com/system/resources/previews/001/189/063/non_2x/star-rounded-png.png",
  iconWidth = 10, iconHeight = 10
  #iconAnchorX = 20, iconAnchorY = 20
  
)

label_world <- label_world %>% filter(ranking > 20) 
  
m <- leaflet(width=1200, height=800) %>% addTiles()  
m %<>%  addCircleMarkers(label_world$Long, label_world$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_world$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_world$txt,
                        group = "World"
                        ) %>%
  
  addCircleMarkers(label_top$Long, label_top$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_top$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_top$txt,
                        group = "Top 20 Countries"
                        ) %>%
  
  addLabelOnlyMarkers(label_top$Long, label_top$Lat, label = label_top$ranking,
                      labelOptions = labelOptions(noHide = TRUE, textOnly = TRUE, 
                                                  direction = "head", 
                                                  offset = c(5,4)),
                      group = "Top 20 Countries") %>%
  
  addLegend("bottomright", pal = wpal, values = label_world$avg_temp, opacity = 1,
            labFormat = labelFormat(suffix = " °C"),
            title = "Temperature") %>% 
  
  addLayersControl(
    #baseGroups = c("OSM (default)", "Toner", "Toner Lite"),
    overlayGroups = c("Top 20 Countries", "World"),
    options = layersControlOptions(collapsed = FALSE)
  )
m

NA

#Top 20 with gdp

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

data.longGDP <- df_gdp %>% gather(key=year, value=GDP, -c(country))
data.top <- data.latest %>% filter(country!='World'& country!='Others')
#data.top <- head(data.top,20)
View(data.top)
data.gdp <- filter(data.longGDP,year=='2020')
View(data.gdp)

#rank GDP

Warning message:
Unknown or uninitialised column: `Country`. 

data.top.hight <- data.gdp %>% select(country, year,GDP) %>%
  mutate(ranking = dense_rank(desc(GDP)))
#data.top.hight

k <- 20

top.gdp <- data.top.hight %>% 
  filter(ranking < k) %>% 
  arrange(ranking)


data.top.low <- data.gdp %>% select(country, year,GDP) %>%
  mutate(ranking = dense_rank(GDP))
low.gdp.long <- data.top.low %>% 
  filter(ranking < k) %>% 
  arrange(ranking)
View(low.gdp.long)


top.gdp

low.gdp

world.gdp <- df_gdp %>% filter(country == "World") %>%
  gather(key = Year, value = GDP, -country)
world.gdp

world.gdp$group <- cut(world.gdp$GDP, c(-Inf,0, Inf), labels = c("-","+"))

g <- ggplot(world.gdp, aes(x = Year, y = GDP, fill = group)) +
    geom_text(aes(label=GDP, y = GDP), size=3, vjust=-1) +
    labs(title=paste0("Real GDP growth (Annual percent change) of World from 2012-2021"))+
    geom_bar(stat = "identity")+
    scale_fill_manual(values = c("-" = "red","+" = "limegreen"))+
    theme_bw() + theme(legend.position = "none")+
    xlab("")+
    theme(#legend.title=element_blank(),
    plot.title = element_text(size=7),
    axis.text=element_text(size=8))

g

gly.world.gdp <- ggplotly(g)
gly.world.gdp

gdp.top20 <- df_gdp2019 %>%

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

  select(c("rank", "country", "GDP (millions of US dollars)")) %>%
  merge(data.latest.all %>% 
          select(country, ranking, confirmed, recovered, deaths) %>% 
          filter(country %in% top.countries & country != "World"), by = "country") %>%
  arrange(ranking) %>% 
  mutate(ranking = ranking - 1) 
gdp.top20 %<>% rename("GDP" = "GDP (millions of US dollars)")
gdp.top20

g <- ggplot(gdp.top20, aes(x = GDP, y = reorder(country, -ranking))) +
  geom_histogram(stat = "identity", aes(fill = GDP))+ 
  scale_fill_gradient("GDP", low = "#FF4038", high = "#50E952") + 
  labs(title=paste0("GDP  of Top 20 Countries in 2019 (millions of US dollars)")) +
  geom_text(aes(label=GDP, x = GDP), size=3.5, hjust=-0.2) +
  xlab("GDP (millions of US dollars)") +
  ylab("") +
  theme(legend.title=element_blank())

Ignoring unknown parameters: binwidth, bins, pad

g

gly.top.gdp <- ggplotly(g)
gly.top.gdp

df_sars <- tbl(my_db, sql("select * from sars_2003"))

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

df_sars <- as.data.frame(df_sars)
df_sars

dates.s <- df_sars[,1]%>% mdy()
range(dates.s)

[1] "2003-03-17" "2003-07-11"

min.date.s <- min(dates.s)
max.date.s <- max(dates.s)
min.date.txt.s <- min.date.s %>% format('%d %b %Y')
max.date.txt.s <- max.date.s %>% format('%d %b %Y')

# clean data Sars 
data.sars <- df_sars %>% rename(c("date" = "Date", "confirmed" = "Cumulative_number"  ,"deaths" = "Number_deaths", "recovered" = "Number_recovered")) %>%
  mutate(date = date %>% mdy()) %>%
  group_by(country, date) %>% as.data.frame() 
View(data.sars)


# Add World's Sars cases 
world.sars <- data.sars %>% group_by(date) %>% 
  summarise(country='World',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T),
            recovered = sum(recovered, na.rm=T))

data.sars %<>% rbind(world.sars)
data.sars %<>% mutate(current.confirmed = confirmed - deaths - recovered)
#View(world.sars) 
#View(data.sars) 

#rate
data.sars %<>% arrange(country, date)
n <- nrow(data.sars)
day1.sars <- min(data.sars$date)
data.sars %<>% mutate(new.confirmed = ifelse(date == day1.sars, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1.sars, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1.sars, NA, recovered - lag(recovered, n=1)))
data.sars %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data.sars %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data.sars %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data.sars %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

View(data.sars)

## convert from wide to long format

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 

data.sars.long <- data.sars %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.sars.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
View(data.sars.long)

# World sars' long data 
world.sars.long <- data.sars.long %>%
  filter(country == "World")
View(world.sars.long)

g <- ggplot(world.sars.long, aes(date, count, color = type)) +
  geom_line()+
  labs(title = "Number of Cases Worldwide: SARs")+
  xlab("")+
  ylab("")
g

gly.g <- ggplotly(g)
gly.g

gly.plot2

## Current Confirmed Cases

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

data.sars.world <- data.sars %>% filter(country=='World')
View(data.sars.world)
n <- nrow(data.sars.world)
View(data.sars.world)
plot1 <- ggplot(data.sars.world, aes(x=date, y=current.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Current Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.world, aes(x=date, y=new.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Daily New Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show two plots side by side
grid.arrange(plot1, plot2, ncol=2)

## a scatter plot with a smoothed line and vertical x-axis labels

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 

plot1 <- ggplot(data.sars.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.sars.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot3 <- ggplot(data.sars.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot4 <- ggplot(data.sars.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot1, plot2, plot3, plot4, nrow=2)

#df_thai <- tbl(my_db, sql("select * from covid_thailand_updated"))
#df_thai <- as.data.frame(df_thai)

df_thai <- read_csv("D:/4D-2/Project 2/Data/covid_thailand_updated.csv")
Sys.setlocale("LC_CTYPE", "Thai")
options(encoding = "UTF-8")
#View(df_thai)

dates.th <- df_thai[,2]%>% mdy()
range(dates.th)
min.date.th <- min(dates.th)
max.date.th <- max(dates.th)
min.date.txt.th <- min.date.th %>% format('%d %b %Y')
max.date.txt.th <- max.date.th %>% format('%d %b %Y')

df_thai$announce_date <- mdy(df_thai$announce_date)
df_thai$notification_date <- mdy(df_thai$notification_date)
df_thai

df_thai <- df_thai %>% select(!No.) %>% 
  group_by(announce_date)
df_thai

# Total confirmed cases in Thailand
data.thai.count <- df_thai %>%
  select(announce_date) %>%
  summarise(comfirmed = n())  %>% as.data.frame()
data.thai.count

data.thai.count$cumulative_confirmed <- cumsum(data.thai.count[, 2])
data.thai.count 

g.th <- ggplot(data.thai.count) + 
  geom_line(aes(x = announce_date, y = cumulative_confirmed)) +
  labs(title = "Thai Confirmed Cases (Jan 2020 - Jan 2021)") +
  xlab("Date (Announce Date)") + 
  ylab("Confirmed Cases")

g.th

gly.th <- ggplotly(g.th)
gly.th

# Confirmed cases divided by sex (gender)
data.thai.gender <- df_thai %>%
  group_by(sex) %>%
  summarise(count = n()) %>%
  mutate(percent = (count / sum(count) * 100) %>% round(2)) %>%
  #mutate(pos = cumsum(percent) - 0.5*percent) %>%
  arrange(desc(percent))
data.thai.gender

data.thai.gender$sex <- factor(data.thai.gender$sex, levels = as.character(data.thai.gender$sex))
data.thai.gender$sex

g.th.gender <- data.thai.gender %>% 
  ggplot(aes(x = "", y = percent, fill = sex)) +
  geom_bar(stat = "identity", width = 1) +
  coord_polar("y") +
  theme_void() +
  geom_text(aes(label = paste0(percent, "%")), color = "white", size = 5, position = position_stack(vjust = 0.5)) +
  guides(fill = guide_legend(reverse = TRUE)) 

g.th.gender

# Confirmed cases divided by age
data.thai.age <- df_thai %>%
  group_by(age) %>%
  summarise(count = n()) %>%
  arrange(desc(count))
data.thai.age

# Confirmed cases divided by nationality
data.thai.nationality <- df_thai %>%
  group_by(nationality) %>%
  summarise(count = n()) %>%
  arrange(desc(count))
data.thai.nationality

# Data in US

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 

df_us <- tbl(my_db, sql("select * from covidus"))
df_us <- as.data.frame(df_us)

df_us_pop <- tbl(my_db, sql("select * from data_population")) 
df_us_pop <- as.data.frame(df_us_pop)

df_us_gender <- tbl(my_db, sql("select * from data_gender")) 
df_us_gender <- as.data.frame(df_us_gender)

df_us_ethnic <- tbl(my_db, sql("select * from data_ethnic")) 
df_us_ethnic <- as.data.frame(df_us_ethnic)

df_us_lockdown <- tbl(my_db, sql("select * from data_lockdown")) 
df_us_lockdown <- as.data.frame(df_us_lockdown)

df_us_health <- tbl(my_db, sql("select * from data_health")) 
df_us_health <- as.data.frame(df_us_health)

df_us_testing <- tbl(my_db, sql("select * from data_testing")) 
df_us_testing <- as.data.frame(df_us_testing)

df_us_latlong <- tbl(my_db, sql("select * from us_latlong"))
df_us_latlong <- as.data.frame(df_us_latlong)

#View(df_us_latlong)

data.us <- df_us %>% select(date, state, cases, deaths) %>%
  mutate(date = date %>% mdy()) %>%
  rename("confirmed" = "cases")
#data.us

dates.us <- data.us[,1]
range(dates.us)

[1] "2020-01-21" "2020-12-24"

min.date.us <- min(dates.us)
max.date.us <- max(dates.us)
min.date.txt.us <- min.date.us %>% format('%d %b %Y')
max.date.txt.us <- max.date.us %>% format('%d %b %Y')
day1.us <- min(data.us$date)

data.us.total <- data.us %>% group_by(date) %>%
  summarise(state='US',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T))

`summarise()` ungrouping output (override with `.groups` argument)

#View(data.us.total)
data.us %<>% rbind(data.us.total)
View(data.us)

data.us.long <- data.us %>% 
  gather(key = type, value = count, -c(date, state)) 
#data.us.long

us.total <- data.us.total %>%
  mutate(new.confirmed = ifelse(date == day1, 0, confirmed - lag(confirmed, n=1)),
                new.deaths = ifelse(date == day1, 0, deaths - lag(deaths, n=1)))

us.total %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths))
us.total

us.total.long <- us.total %>% 
  gather(key = type, value = count, -c(date, state))

View(us.total.long)

g1 <- data.us.long %>% filter(state == "US") %>%

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 

  ggplot(aes(x = date, y = count)) +
  geom_area(aes(fill=type), alpha=0.5) +
  labs(title = paste0("Cumulative cases in US : ", min.date.txt.us, '-', max.date.txt.us, " (Log Scale)"))  +
  scale_y_continuous(trans='log10')+
  scale_fill_manual(values=c('red', 'black'))+
  ylab("")

g2 <- us.total.long %>%
  filter(type %in% c("new.confirmed")) %>%
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() + 
  labs(title = paste0("Daily confirmed cases in US : ", min.date.txt.us, '-', max.date.txt.us)) +
  xlab("Date") +
  ylab("Confirmed cases")

gly.us1 <- ggplotly(g1)

Transformation introduced infinite values in continuous y-axis

gly.us2 <- ggplotly(g2)

gly.us1

gly.us2

NA

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

data.us %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)))

data.us %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths))
View(data.us)

#data.us.daily <- data.us %>% filter(state == "US")
#View(data.us.daily)

data.us.pop <- df_us_pop %>% select(State, Population) %>%
  rename("state" = "State") 
#data.us.pop

data.us.gender <- df_us_gender %>% select(State, Male, Female) %>%
  rename("state" = "State") 
data.us.gender

data.us.latest <- data.us %>%
  filter(date == max.date.us) %>% 
  merge(data.us.pop, by = "state", all.x = T) %>%
  merge(data.us.gender, by = "state", all.x = T)
#View(data.us.latest)

data.us.latest$Population[data.us.latest$state == "US"] <- sum(data.us.pop$Population)
data.us.latest %<>% mutate(ranking = dense_rank(desc(confirmed)),
                           confirmed.rate = (100 * confirmed / Population) %>% round(2),
                           death.rate = (100 * deaths / confirmed) %>% round(2),
                           Male.confirmed = (Male * confirmed) %>% round(0),
                           Female.confirmed = Female * confirmed %>% round(0),
                           Male.deaths = Male * deaths,
                           Female.deaths = Female * deaths) %>%
  arrange(ranking) 

top.us <- data.us.latest[,1]
top.us

 [1] "US"                       "California"               "Texas"                    "Florida"                  "Illinois"                
 [6] "New York"                 "Ohio"                     "Georgia"                  "Pennsylvania"             "Tennessee"               
[11] "Michigan"                 "Wisconsin"                "North Carolina"           "Indiana"                  "Arizona"                 
[16] "New Jersey"               "Minnesota"                "Missouri"                 "Massachusetts"            "Alabama"                 
[21] "Virginia"                 "Colorado"                 "Louisiana"                "South Carolina"           "Iowa"                    
[26] "Oklahoma"                 "Maryland"                 "Utah"                     "Kentucky"                 "Washington"              
[31] "Kansas"                   "Nevada"                   "Arkansas"                 "Mississippi"              "Connecticut"             
[36] "Nebraska"                 "Idaho"                    "New Mexico"               "Oregon"                   "Puerto Rico"             
[41] "South Dakota"             "North Dakota"             "Rhode Island"             "Montana"                  "West Virginia"           
[46] "Delaware"                 "Alaska"                   "Wyoming"                  "New Hampshire"            "District of Columbia"    
[51] "Maine"                    "Hawaii"                   "Guam"                     "Vermont"                  "Virgin Islands"          
[56] "Northern Mariana Islands"

data.us.latest

NA
NA

# List of top 20 state

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 

k <- 20
data.us.top <- data.us.latest %>%
  filter(ranking <= k+1) %>% 
  arrange(ranking) 

us.state.top <- data.us.top %>% pull(state) %>% as.character()
us.state.top  %>% setdiff('US') %>% print()

 [1] "California"     "Texas"          "Florida"        "Illinois"       "New York"       "Ohio"           "Georgia"        "Pennsylvania"  
 [9] "Tennessee"      "Michigan"       "Wisconsin"      "North Carolina" "Indiana"        "Arizona"        "New Jersey"     "Minnesota"     
[17] "Missouri"       "Massachusetts"  "Alabama"        "Virginia"      

# confirmed rate & death rate of top 20 state
g.rate <- data.us.latest %>% filter(state %in% us.state.top & state != "US") %>%
  select(state, confirmed.rate, death.rate, ranking) %>%
  gather(key = Type, value = Percent, -c(state, ranking)) %>%
  ggplot(aes(x=reorder(state, -desc(ranking)), y=Percent, fill = Percent)) +
  geom_bar(stat='identity') +
  scale_fill_gradient(low = "#ebbc62", high = "#b42006") +
  geom_text(aes(label=Percent, y=Percent), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Confirmed Rate & Death Rate of Top 20 State in US')) +
  #scale_fill_continuous(name='State', labels=aes(Percent)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~Type, ncol=1, scales='free_y') 

g.rate

us.confirmed.num <- data.us.top$confirmed[data.us.top$state == "US"] %>% as.numeric()

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 
5: Unknown or uninitialised column: `Country`. 
6: Unknown or uninitialised column: `Country`. 

us.confirmed.num

[1] 18706036

data.us.top %<>% mutate(confirmed.per.us = (confirmed * 100 / us.confirmed.num) %>% round(1))
data.us.top

g.us.top1 <- data.us.top %>%
  filter(state != "US") %>%
  select(state, confirmed, confirmed.per.us, ranking) %>%
  gather(key = Type, value = count, -c(state, ranking, confirmed.per.us)) %>%
  
  ggplot(aes(fill = count, y = count, x = reorder(state, -desc(ranking)))) + 
  geom_bar(position = "dodge", stat = "identity") +
  labs(title = "20 state in US with most confirmed cases") +
  scale_fill_gradient(low = "#ebbc62", high = "#b42006") +
  xlab("") + 
  ylab("Confirmed Cases") +
  geom_text(aes(label=paste0(confirmed.per.us, "%")), size=3, vjust=-0.5) +
  theme(axis.text.x = element_text(angle = 90,vjust = 0.5))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

g.us.top2 <- data.us.top %>%
  filter(state != "US") %>%
  select(state, Male.confirmed, Female.confirmed, Male.deaths, Female.deaths, ranking) %>%
  gather(key = Type, value = count, -c(state, ranking)) %>%
  
  ggplot(aes(fill = Type, y = count, x = reorder(state, -desc(ranking)))) + 
  geom_bar(position = "stack", stat = "identity") +
  labs(title = "20 most confirmed cases's state in US (Divided by gender)") +
  xlab("") + 
  ylab("Cases") +
  theme(axis.text.x = element_text(angle = 45,vjust = 1)) +
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

g.us.top3 <-  data.us.top %>%
  filter(state != "US") %>%
  select(state, confirmed.per.us, ranking) %>%
  gather(key = Type, value = count, -c(state, ranking)) %>%
  ggplot(aes(x = "", y = confirmed.per.us, fill = confirmed.per.us)) +
  geom_bar(stat = "identity", width = 1) +
  coord_polar("y") +
  theme_void() +
  geom_text(aes(label = paste0(percent, "%")), color = "white", size = 5, position = position_stack(vjust = 0.5)) +
  guides(fill = guide_legend(reverse = TRUE))

g.us.top1

gly.us.top1 <- ggplotly(g.us.top1)
gly.us.top1

NA
NA

data.us.latlong <- df_us_latlong %>%

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 
4: Unknown or uninitialised column: `Country`. 

  rename("lat" = "latitude", "long" = "longitude")

#label_world <- lat.long 
#label_world$avg_temp <- as.numeric(label_world[, names(label_world) %in% c("avg_temp")])
#label_world <- label_world %>%  
#  mutate(txt=paste0('<b>',ranking, '</b>',
#                    '<br/>','<b>',country, '</b>',
#                    '<br/>', "Temperature:  ",avg_temp, ' °C',
#                    '<br/>', "Confirmed:  ", confirmed, 
#                    '<br/>', "Deaths: ", deaths,
#                    '<br/>', "Recovered: ", recovered
#                    )) 

#label_world$txt <- label_world$txt %>% lapply(htmltools::HTML)
#label_world

map.us <- data.us.latest %>%
  select(state, confirmed, confirmed.rate, deaths, death.rate, ranking) %>%
  merge(data.us.latlong) %>%
  mutate()
map.us

mergcountry = function(data1,data2){
  data <- merge(x = data1, y = data2, by = "country", all.x = TRUE) 
  return(data)
}
data.top.world <- merge(x = data.top, y = df_gdp2019, by = "country", all.x = TRUE) %>% 
  select(-c(code,rank,new.confirmed,new.deaths,current.confirmed,population)) %>% 
  rename(GDP="GDP (millions of US dollars)")

data.top.world <- merge(x = data.top.world, y = df_healt, by = "country", all.x = TRUE) %>%
  rename(healthcare="healthCareIndex")

data.top.world <- merge(x = data.top.world, y = df_pornhub, by = "country", all.x = TRUE) %>%
  rename(Pornhub = "PornhubIndex(%)")

data.top.world <- mergcountry(data.top.world, df_temp)
index <- is.na(data.top.world)
data.top.world[index] <- 0
data.top.world

#View(data.top.world)

normalize = function(data){
  #return ((data - min(data,na.rm = TRUE))/(max(data,na.rm = TRUE) - min(data,na.rm = TRUE)))
  z <- scale(data);
  tanh(z/2)
}

norm_data = as.data.frame(apply(data.top.world[,2:12],2,normalize))
corr_data <- norm_data
norm_data$country <- c("Argentina","Bangladesh","Brazil","Chile","Colombia","France","Germany","India","Iran","Italy","Mexico","Pakistan","Peru","Russia","saudi Arabia","South Africa","Spain","Turkey","United Kingdom","US")
View(norm_data)
norm_data_plot <- select(norm_data,"country","confirm.rate","death.rate","recover.rate","healthcare","Pornhub","GDP","avg_temp")
norm_data_plot %<>% gather(key=type, value=count, -c(country))
level_order <- factor(norm_data_plot$type, 
                      level = c("GDP","avg_temp","healthcare","recover.rate","death.rate","confirm.rate","Pornhub"))
ggplot(data = norm_data_plot, aes(x=country, y=level_order, fill=count)) + 
  geom_tile() +
  scale_fill_gradient(low = "pink", high = "blue") +
  xlab("") +
  ylab("") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 90,vjust = 1))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

#correlation
corr_data %<>% select(c(GDP,confirm.rate,death.rate,recover.rate, healthcare, avg_temp, Pornhub))
head(corr_data)
cor(corr_data)
ggcorrplot(cor(corr_data),hc.order = TRUE,
           outline.color = "white",
           colors = c("#6D9EC1","white","#E46726"),
           lab = TRUE)